package com.msg.xml;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

/*
 * this class provide the handler for database reading and writing
 */
public class DBHandler {
	private static DBHandler instance;
	private Connection conn = null;
	private DBHandler(){}
	
	/**
	 * create a singleton for DB connection
	 * @param userName : the username for the connection to database
	 * @param password : the password for the connection to database
	 * @param url : the url path of the database connection
	 * @return
	 */
	public static DBHandler createInstance(String userName, String password, String url) {
		try {
			instance = new DBHandler();
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			instance.conn = DriverManager.getConnection (url, userName, password);
			assert(instance.conn != null);
		}
		catch(Exception ex) {
			ex.printStackTrace();
		}
		return instance;
	}
	
	public static DBHandler getInstance() {
		return instance;
	}
	
	/**
	 * after using the database handler, we need shut it down
	 */
	public static void closeDB() {
		try {
			instance.conn.close();
		}
		catch(Exception ex) {
			ex.printStackTrace();
		}
		instance = null;
	}
	
	/**
	 * check is a data has already exist in the table
	 * @param tableName
	 * @param request : data wrapper which contains the name of columns and their data
	 * @return
	 */
	public boolean hasData(String tableName, DBRequest request) {
		//create the query
		StringBuilder query = new StringBuilder("SELECT ");
		addResultColumn(query, request);
		query.append(" FROM " + tableName + " WHERE ");
		addEntryDataPair(query, request, "AND");
		ArrayList<String> result = executeQuery(query.toString(), request.resultCol);
		if(result.size() == 0)
			return false;
		else
			return true;
	}
	
	/**
	 * create a query and call the executeQuery method to get the data from tables
	 * @param tableName
	 * @param request : data wrapper which contains the name of columns and their data
	 * @return
	 */
	public ArrayList<String> getData(String tableName, DBRequest request) {	
		//create the query
		StringBuilder query = new StringBuilder("SELECT ");
		addResultColumn(query, request);
		query.append(" FROM " + tableName + " WHERE ");
		addEntryDataPair(query, request, "AND");
		return executeQuery(query.toString(), request.resultCol);
	}
	
	
	/**
	 * insert a new data row into the database
	 * @param tableName
	 * @param request : data wrapper which contains the name of columns and their data
	 */
	public void insertData(String tableName, DBRequest request) {
		//create the query
		StringBuilder query = new StringBuilder("INSERT INTO ");
		query.append(tableName + " (");
		addEntry(query, request);
		query.append(") VALUES (");
		addData(query, request);
		query.append(")");
		executeUpdate(query.toString());
	}
	
	/**
	 * updating exist records in tables and adding new values into it
	 * @param tableName
	 * @param request
	 */
	public void updateData(String tableName, DBRequest request) {
		StringBuilder query = new StringBuilder("UPDATE ");
		query.append(tableName + " SET ");
		addResultColPair(query, request, ",");
		query.append(" WHERE ");
		addEntryDataPair(query, request, "AND");
		executeUpdate(query.toString());
	}
	
	/**
	 * run the query and return the values of a single field
	 * @param query
	 * @return, a list of data
	 */
	public ArrayList<String> runQuery(String query) {
		ArrayList<String> result = new ArrayList<String>();
		ResultSet rs = null;
		try {			
			//execute query and get result
			Statement s = conn.createStatement();
			s.executeQuery(query);
			rs = s.getResultSet();
			while(rs.next()) {
				result.add(rs.getString(1));
			}
		}
		catch(Exception ex) {
			ex.printStackTrace();
		}
		return result;
	}
	
	/**
	 * a function specifically used to get data from tables
	 * e.g. SELECT * FROM Event
	 * @param query
	 * @param reqCols : the columns' name of SELECT COMMAND
	 * @return
	 */
	public ArrayList<String> executeQuery(String query, ArrayList<String> reqCols) {
		ArrayList<String> result = new ArrayList<String>();
		ResultSet rs = null;
		
		try {			
			//execute query and get result
			Statement s = conn.createStatement();
			s.executeQuery(query);
			rs = s.getResultSet();
			while(rs.next()) {
				StringBuilder tmp = new StringBuilder();
				for(int i = 0; i < reqCols.size()-1; i++) {
					tmp.append(rs.getString(reqCols.get(i)));
					tmp.append(",");
				}
				tmp.append(rs.getString(reqCols.get(reqCols.size()-1)));
				result.add(tmp.toString());
			}
			rs.close();
			s.close();
		}
		catch(Exception ex) {
			ex.printStackTrace();
		}
		return result;
	}
	
	/**
	 * a method used to update the contents in tables
	 * e.g. UPDATE, DELETE, INSERT..
	 * @param query
	 */
	private void executeUpdate(String query) {
		try {
			//execute query and get result
			Statement s = conn.createStatement();
			s.executeUpdate(query);
		}
		catch(Exception ex) {
			ex.printStackTrace();
		}
	}
	
	/**
	 * add the column names and column data to create a conditional statement
	 * e.g. select * from *** where entries = data
	 * @param query
	 * @param request
	 */
	private void addEntryDataPair(StringBuilder query, DBRequest request, String separator) {
		addPair(query, request.entries, request.data, separator);
	}
	
	/**
	 * add the result column and result column data pair into query
	 * e.g. update *** set resultCol = resultColData *** 
	 * @param query
	 * @param request
	 */
	private void addResultColPair(StringBuilder query, DBRequest request, String separator) {
		addPair(query, request.resultCol, request.resultColData, separator);
	}
	

	/**
	 * add the column and data pair to query
	 * e.g. WHERE event_Id = 1 opponents = LAKERS
	 * or  set event_code = EFL AND opponents = Celtics
	 * @param query : the query will be executed 
	 * @param list1 : the list with column names
	 * @param list2 : the list with column data
	 */
	private void addPair(StringBuilder query, ArrayList<String> list1, ArrayList<String> list2,
											 String separator) {
		int index = 0;
		for(; index < list1.size()-1; index++) {
			query.append(list1.get(index));
			query.append(" = ");
			query.append('\'' + list2.get(index) + '\'');
			query.append(" " + separator + " ");
		}
		query.append(list1.get(index));
		query.append(" = ");
		query.append('\''+ list2.get(index) + '\'');
	}
	
	/**
	 * generate a combination of data
	 * e.g. event_Id, event_Date, opponentName
	 * e.g. "EFL33", "12:33", "LAKER"
	 * @param query : the query need add data combination
	 * @param list : the list contains the data in the combination
	 * @param needQuatation : decide if the string need to be in quotation (e.g. Minnesota Lynx)
	 */
	private void addCombination(StringBuilder query, ArrayList<String> list, boolean needQuotation) {
		int index = 0;
		String quotation = "";
		if(needQuotation)
			quotation = "\'";
		for(; index < list.size()-1; index++) {
			query.append(quotation + list.get(index) + quotation);
			query.append(", ");
		}
		query.append(quotation + list.get(index) + quotation);
	}
	
	/**
	 * all addCombination to add entries into the query
	 * @param query
	 * @param request : data wrapper which contains the entries and data of these entries
	 */
	private void addEntry(StringBuilder query, DBRequest request) {
		addCombination(query, request.entries, false);
	}
	
	/**
	 * all addCombination to add data
	 * @param query
	 * @param request
	 */
	private void addData(StringBuilder query, DBRequest request) {
		addCombination(query, request.data, true);
	}
	
	/**
	 * all addCombination to result columns into the query
	 * @param query
	 * @param request
	 */
	private void addResultColumn(StringBuilder query, DBRequest request) {
		addCombination(query, request.resultCol, false);
	}
	
	public static void main(String args[]) {
		DBHandler.createInstance("root", "guoyu1989", "jdbc:mysql://localhost/nyliberty");
		try {
			Statement s = instance.conn.createStatement();
			s.executeUpdate("INSERT INTO price_code (Event_Id, Ticket_Type_Id, Price_Level_Id, Ticket_Level_Id, price) VALUES ('2', '2', '11', '6', '0.00')");
		}
		catch(Exception ex){
			ex.printStackTrace();
		}
	}
}
